import hashlib
from collections import defaultdict
from functools import lru_cache

import numpy as np
from flask import Blueprint, render_template, redirect, url_for, flash
from flask_login import LoginManager, UserMixin, login_user, login_required, logout_user
from flask_login import current_user
from python.extensions import db
from python.model.goods import Goods
from flask import jsonify
import pymysql
from pyecharts.charts import Page, Pie, Bar, Radar, Boxplot
from pyecharts import charts
from pyecharts  import options as opts
from flask import Blueprint, request, jsonify
import logging
from pyecharts.commons.utils import JsCode

from flask import render_template, request
from pyecharts import options as opts
from pyecharts.charts import Bar, Line, Pie, Page, Timeline, Sankey
import traceback

main_bp = Blueprint('main', __name__)
menu_bp = Blueprint('menu', __name__, url_prefix='/menu')

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 数据库配置
DB_CONFIG = {
    'host': 'mysql5.sqlpub.com',
    'user': 'root133',
    "port": 3310,
    'password': 'xSipNzqyvtBioMdU',
    'database': 'stat133',
    'charset': 'utf8',
}


def get_db_connection():
    return pymysql.connect(**DB_CONFIG)
@main_bp.route('/')
@login_required
def index():
    return render_template('index.html', username=current_user.username)


@menu_bp.route('/bar_example')
def bar_example():
    return render_template('menu/bar_example.html')

@menu_bp.route('/shouye')
def shouye():
    """首页路由"""
    return render_template('menu/shouye.html')



@menu_bp.route('/sousuo')
def sousuo():
    api_url = url_for('menu.get_car_sales')
    logger.info(f"Rendering sousuo page with API URL: {api_url}")
    return render_template('menu/sousuo.html', api_url=api_url)


@menu_bp.route('/api/car_sales', methods=['GET'])
def get_car_sales():
    try:
        # 获取请求参数
        search = request.args.get('search', '')
        year = request.args.get('year', '')
        month = request.args.get('month', '')
        price_range = request.args.get('price_range', '')
        sales_range = request.args.get('sales_range', '')
        sort = request.args.get('sort', 'default')
        page = request.args.get('page', '1')
        per_page = request.args.get('per_page', '10')

        # 验证和转换参数
        try:
            page = int(page)
            per_page = int(per_page)
        except ValueError:
            return jsonify({'error': '无效的页码或每页数量'}), 400

        # 记录请求参数
        logger.info(f"API调用参数: search={search}, year={year}, month={month}, price_range={price_range}, "
                    f"sales_range={sales_range}, sort={sort}, page={page}, per_page={per_page}")

        # 构建SQL查询
        base_query = """
            SELECT id, year, month, ranking AS ranks, model, manufacturer, 
                   sales_volume AS sales, price_range AS price
            FROM cars
            WHERE 1=1 
        """
        count_query = "SELECT COUNT(*) AS total FROM cars WHERE 1=1"
        conditions = []
        params = []

        # 添加筛选条件
        if search:
            conditions.append("(model LIKE %s OR manufacturer LIKE %s)")
            params.extend([f"%{search}%", f"%{search}%"])

        if year:
            conditions.append("year = %s")
            params.append(year)

        if month:
            conditions.append("month = %s")
            params.append(month)

        if price_range:
            try:
                min_price, max_price = price_range.split('-')
                conditions.append("price_range BETWEEN %s AND %s")
                params.extend([float(min_price), float(max_price)])
            except (ValueError, TypeError):
                return jsonify({'error': '价格范围格式错误'}), 400

        if sales_range:
            try:
                min_sales, max_sales = sales_range.split('-')
                conditions.append("sales_volume BETWEEN %s AND %s")
                params.extend([int(min_sales), int(max_sales)])
            except (ValueError, TypeError):
                return jsonify({'error': '销量范围格式错误'}), 400

        # 添加条件到查询
        if conditions:
            condition_str = " AND " + " AND ".join(conditions)
            print(condition_str)
            base_query += condition_str
            count_query += condition_str

        # 添加排序
        sort_map = {
            'default': "year DESC, month DESC, ranking ASC",
            'price-asc': "price_range ASC",
            'price-desc': "price_range DESC",
            'sales-asc': "sales_volume ASC",
            'sales-desc': "sales_volume DESC"
        }
        sort_order = sort_map.get(sort, sort_map['default'])
        base_query += f" ORDER BY {sort_order}"

        # 添加分页
        offset = (page - 1) * per_page
        base_query += " LIMIT %s OFFSET %s"

        # 使用相同参数获取总数
        count_params = params.copy()

        try:
            # 连接数据库
            logger.info(f"连接到数据库: {DB_CONFIG['host']}:{DB_CONFIG['port']}")
            connection = pymysql.connect(
                host=DB_CONFIG['host'],
                port=DB_CONFIG['port'],
                user=DB_CONFIG['user'],
                password=DB_CONFIG['password'],
                database=DB_CONFIG['database'],
                charset=DB_CONFIG['charset'],
                cursorclass=pymysql.cursors.DictCursor
            )

            with connection.cursor() as cursor:
                # 获取总数
                logger.info(f"执行计数查询: {count_query} 参数: {count_params}")
                cursor.execute(count_query, count_params)
                count_result = cursor.fetchone()
                total = count_result['total'] if count_result else 0

                # 获取数据
                query_params = params + [per_page, offset]
                logger.info(f"执行数据查询: {base_query} 参数: {query_params}")
                cursor.execute(base_query, query_params)
                data = cursor.fetchall()

            logger.info(f"查询成功. 找到 {total} 条记录, 返回 {len(data)} 条记录")
            return jsonify({
                'total': total,
                'data': data
            })

        except pymysql.Error as e:
            logger.error(f"数据库错误: {str(e)}")
            return jsonify({'error': f"数据库错误: {str(e)}"}), 500
        except Exception as e:
            logger.error(f"意外错误: {str(e)}")
            return jsonify({'error': f"意外错误: {str(e)}"}), 500
        finally:
            if connection:
                connection.close()
                logger.info("数据库连接已关闭")

    except Exception as e:
        logger.error(f"API处理错误: {str(e)}")
        return jsonify({'error': f"服务器错误: {str(e)}"}), 500




@menu_bp.route('/Top10_month')
def Top10_month():
    conn = get_db_connection()
    try:
        # 获取所有年份和月份
        with conn.cursor() as cursor:
            cursor.execute("SELECT DISTINCT year FROM cars ORDER BY year DESC")
            years = [row[0] for row in cursor.fetchall()] or [2023]

            cursor.execute("SELECT DISTINCT month FROM cars ORDER BY month")
            months = [row[0] for row in cursor.fetchall()] or list(range(1, 13))

        # 从请求参数读取当前选择的年份、月份和数据类型
        year = request.args.get('year', default=years[0], type=int)
        month = request.args.get('month', default=9, type=int)
        data_type = request.args.get('type', default='厂商')

        # --------- 柱状图数据查询 ---------
        with conn.cursor() as cursor:
            if data_type == "厂商":
                sql = """
                    SELECT SUM(sales_volume) AS total_sales, manufacturer
                    FROM cars
                    WHERE year = %s AND month = %s
                    GROUP BY manufacturer
                    ORDER BY total_sales DESC
                    LIMIT 10
                """
            else:
                sql = """
                    SELECT sales_volume, model
                    FROM cars
                    WHERE year = %s AND month = %s
                    ORDER BY sales_volume DESC
                    LIMIT 10
                """
            cursor.execute(sql, (year, month))
            data = cursor.fetchall()

        if data_type == "厂商":
            bar_x = [row[1] for row in data]
            bar_y = [row[0] for row in data]
        else:
            bar_x = [row[1] for row in data]
            bar_y = [row[0] for row in data]

        bar = (
            Bar(init_opts=opts.InitOpts(width="100%", height="600px"))
            .add_xaxis(bar_x)
            .add_yaxis(
                series_name="销量",
                y_axis=bar_y,
                label_opts=opts.LabelOpts(
                    position="right",
                    formatter=JsCode("function(params){return params.value.toLocaleString() + '辆';}")
                ),
                itemstyle_opts=opts.ItemStyleOpts(
                    color=JsCode("""
                        function(params) {
                            const colors = [
                                '#c23531', '#2f4554', '#61a0a8', '#d48265',
                                '#91c7ae', '#749f83', '#ca8622', '#bda29a'
                            ];
                            return colors[params.dataIndex % colors.length];
                        }
                    """)
                )
            )
            .reversal_axis()
            .set_global_opts(
                title_opts=opts.TitleOpts(title=f"{year}年{month}月{data_type}销量TOP10"),
                tooltip_opts=opts.TooltipOpts(
                    trigger="axis",
                    axis_pointer_type="shadow",
                    formatter=JsCode("""
                        function(params){
                            return params[0].name + '<br/>销量: ' + params[0].value.toLocaleString() + '辆';
                        }
                    """)
                ),
                xaxis_opts=opts.AxisOpts(
                    name="销量（辆）",
                    axislabel_opts=opts.LabelOpts(formatter="{value}辆")
                ),
                yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(interval=0))
            )
        )

        # --------- 折线图：年月均Top10车型销量趋势 ---------
        line_timeline = Timeline(init_opts=opts.InitOpts(width="100%", height="500px"))

        for y in years:
            with conn.cursor() as cursor:
                cursor.execute("""
                    SELECT model FROM cars 
                    WHERE year = %s 
                    GROUP BY model 
                    ORDER BY SUM(sales_volume)/12 DESC 
                    LIMIT 10
                """, (y,))
                top10_models = [row[0] for row in cursor.fetchall()]

                cursor.execute("""
                    SELECT month, model, sales_volume 
                    FROM cars 
                    WHERE year = %s AND model IN %s 
                    ORDER BY month, model
                """, (y, tuple(top10_models)))
                raw_data = cursor.fetchall()

            # 重组数据：车型->月份销量列表
            trend_data = {model: [0] * 12 for model in top10_models}
            for m, model, sales in raw_data:
                trend_data[model][m - 1] = sales

            colors = [
                '#c23531', '#2f4554', '#61a0a8', '#d48265',
                '#91c7ae', '#749f83', '#ca8622', '#bda29a',
                '#6e7074', '#546570'
            ]

            line = (
                Line()
                .add_xaxis([f"{m}月" for m in range(1, 13)])
                .set_global_opts(
                    title_opts=opts.TitleOpts(title=f"{y}年月均Top 10车型销量趋势"),
                    tooltip_opts=opts.TooltipOpts(
                        trigger="axis",
                        formatter=JsCode("""
                            function(params) {
                                let res = params[0].axisValue + '<br/>';
                                params.forEach(p => {
                                    res += `${p.seriesName}: ${p.value.toLocaleString()}辆<br/>`;
                                });
                                return res;
                            }
                        """)
                    ),
                    legend_opts=opts.LegendOpts(
                        orient="horizontal",
                        type_="scroll",
                        pos_left="30%",
                        pos_top="5%",
                        item_gap=20,
                        item_width=40,
                        selected_mode="single"
                    ),
                    xaxis_opts=opts.AxisOpts(
                        name="月份",
                        boundary_gap=False,
                        axislabel_opts=opts.LabelOpts(rotate=30)
                    ),
                    yaxis_opts=opts.AxisOpts(
                        name="销量",
                        axislabel_opts=opts.LabelOpts(formatter="{value}辆"),
                        splitline_opts=opts.SplitLineOpts(is_show=True)
                    ),
                    datazoom_opts=[opts.DataZoomOpts(pos_bottom="4px", pos_top="96%")]
                )
            )

            for idx, (model, sales) in enumerate(trend_data.items()):
                line.add_yaxis(
                    series_name=model,
                    y_axis=sales,
                    is_smooth=True,
                    symbol="circle",
                    symbol_size=8,
                    label_opts=opts.LabelOpts(is_show=False),
                    linestyle_opts=opts.LineStyleOpts(width=2),
                    itemstyle_opts=opts.ItemStyleOpts(color=colors[idx % len(colors)]),
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="峰值"),
                            opts.MarkPointItem(type_="min", name="谷值")
                        ]
                    )
                )

            line_timeline.add(line, time_point=f"{y}年")

        # 时间线自动播放
        line_timeline.add_schema(
            pos_left="20px",
            pos_right="20px",
            pos_top="88%",
            pos_bottom="3%",
            is_auto_play=True,
            play_interval=3000
        )

        # --------- 雷达图部分---------
        # 1. 获取有数据的年份（按年份倒序）
        with conn.cursor() as cursor:
            cursor.execute("""
                            SELECT DISTINCT year 
                            FROM cars 
                            ORDER BY year DESC
                        """)
            years = [row[0] for row in cursor.fetchall()]

        # 2. 定义雷达图维度
        dimensions = [
            {"name": "月均销量", "max": None},  # 动态计算
            {"name": "最低价", "max": None},
            {"name": "最高价", "max": None},
            {"name": "价格跨度", "max": None},
            {"name": "新能源占比(%)", "max": 100}
        ]

        # 3. 创建时间轴容器
        radar_timeline = Timeline(
            init_opts=opts.InitOpts(
                width="1300px",
                height="700px",
                bg_color="#f5f5f5"
            )
        )

        # 4. 颜色配置
        color_palette = [
            '#c23531', '#2f4554', '#61a0a8', '#d48265',
            '#91c7ae', '#749f83', '#ca8622', '#bda29a'
        ]

        # 5. 预计算全局最大值（用于统一坐标轴）
        global_max = {
            'avg_sales': 0,
            'min_price': 0,
            'max_price': 0,
            'price_span': 0
        }

        # 6. 处理各年数据
        year_data = {}
        for year in years:
            with conn.cursor() as cursor:
                # 查询当年各月Top10的月均数据
                cursor.execute("""
                                SELECT 
                                    model,
                                    SUM(sales_volume)/12 as monthly_avg,  # 关键修改：计算月均值
                                    price_range,
                                    manufacturer
                                FROM cars
                                WHERE year = %s
                                GROUP BY model, manufacturer, price_range
                                ORDER BY monthly_avg DESC
                                LIMIT 10
                            """, (year,))

                # 计算指标
                monthly_sales = []
                min_prices = []
                max_prices = []
                new_energy_count = 0

                for row in cursor.fetchall():
                    model, avg_sales, price_str, mfg = row
                    monthly_sales.append(avg_sales)

                    # 解析价格区间
                    try:
                        if '-' in price_str:
                            min_p, max_p = map(float, price_str.split('-'))
                        else:
                            min_p = max_p = float(price_str)
                    except:
                        min_p = max_p = 0

                    min_prices.append(min_p)
                    max_prices.append(max_p)

                    # 识别新能源车型
                    if any(kw in model for kw in ['新能源', '电动', '混动', 'PLUS']):
                        new_energy_count += 1

                # 计算年度指标（基于月均Top10）
                avg_monthly_sales = sum(monthly_sales) / len(monthly_sales) if monthly_sales else 0
                avg_min_price = sum(min_prices) / len(min_prices) if min_prices else 0
                avg_max_price = sum(max_prices) / len(max_prices) if max_prices else 0
                price_span = avg_max_price - avg_min_price
                new_energy_ratio = (new_energy_count / len(monthly_sales)) * 100 if monthly_sales else 0

                # 更新全局最大值
                global_max['avg_sales'] = max(global_max['avg_sales'], avg_monthly_sales)
                global_max['min_price'] = max(global_max['min_price'], avg_min_price)
                global_max['max_price'] = max(global_max['max_price'], avg_max_price)
                global_max['price_span'] = max(global_max['price_span'], price_span)

                # 存储数据
                year_data[year] = {
                    'avg_sales': avg_monthly_sales,
                    'min_price': avg_min_price,
                    'max_price': avg_max_price,
                    'price_span': price_span,
                    'new_energy_ratio': new_energy_ratio
                }

        # 7. 设置动态维度范围（增加20%余量）
        dimensions[0]["max"] = float(global_max['avg_sales']) * 1.2
        dimensions[1]["max"] = float(global_max['min_price']) * 1.2
        dimensions[2]["max"] = float(global_max['max_price']) * 1.2
        dimensions[3]["max"] = float(global_max['price_span']) * 1.2

        # 8. 生成各年度雷达图
        for i, year in enumerate(years):
            data = year_data[year]

            radar = (
                Radar()
                .add_schema(
                    schema=dimensions,
                    splitarea_opt=opts.SplitAreaOpts(is_show=True),
                    textstyle_opts=opts.TextStyleOpts(color="#333")
                )
                .add(
                    series_name="Top10月均指标",
                    data=[[
                        data['avg_sales'],
                        data['min_price'],
                        data['max_price'],
                        data['price_span'],
                        data['new_energy_ratio']
                    ]],
                    color=color_palette[i % len(color_palette)],
                    areastyle_opts=opts.AreaStyleOpts(opacity=0.3),
                    linestyle_opts=opts.LineStyleOpts(width=2),
                    label_opts=opts.LabelOpts(
                        formatter=JsCode("""
                                        function(params) {
                                            return params.value.toFixed(1);
                                        }
                                    """)
                    )
                )
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title=f"{year}年月均Top10车型分析",
                        subtitle="数据基于各月Top10车型的平均值"
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        trigger="item",
                        formatter=JsCode("""
                                        function(params) {
                                            var data = {
                                                '月均销量': params.value[0].toFixed(0) + '辆',
                                                '最低价': params.value[1].toFixed(2) + '万',
                                                '最高价': params.value[2].toFixed(2) + '万',
                                                '价格跨度': params.value[3].toFixed(2) + '万',
                                                '新能源占比': params.value[4].toFixed(1) + '%'
                                            };
                                            return params.seriesName + '<br/>' + 
                                                Object.entries(data).map(
                                                    ([key, val]) => `${key}: ${val}`
                                                ).join('<br/>');
                                        }
                                    """)
                    )
                )
            )

            radar_timeline.add(radar, str(year))

        # 9. 配置时间轴
        radar_timeline.add_schema(
            axis_type="category",
            is_auto_play=True,
            play_interval=2000,
            pos_bottom="10%",
            label_opts=opts.LabelOpts(
                position="bottom",
                font_size=12,
                color="#333",
                formatter=JsCode("""
                                function(value) {
                                    return value + '年';
                                }
                            """)
            )
        )

        # --------- 堆叠面积图，计算月度Top5厂商份额 ---------
        @lru_cache(maxsize=100)
        def get_mfg_color(mfg):
            brand_colors = {
                "比亚迪": "#E32112", "特斯拉": "#E82127",
                "大众": "#003399", "丰田": "#FF0000"
            }
            if mfg in brand_colors:
                return brand_colors[mfg]
            hash_code = hashlib.md5(mfg.encode()).hexdigest()
            r = min(int(hash_code[0:2], 16) % 128 + 128, 255)
            g = min(int(hash_code[2:4], 16) % 128 + 64, 255)
            b = min(int(hash_code[4:6], 16) % 128 + 64, 255)
            return f"#{r:02x}{g:02x}{b:02x}"

        all_months = [f"{m:02d}月" for m in range(1, 13)]

        duidie_timeline = Timeline(init_opts=opts.InitOpts(width="100%", height="500px", bg_color="#f5f5f5"))

        for y in years:
            monthly_shares = {}
            mfg_total_share = {}

            with conn.cursor() as cursor:
                for month_num in range(1, 13):
                    month_str = f"{month_num:02d}月"
                    cursor.execute("""
                        SELECT manufacturer, SUM(sales_volume) as sales
                        FROM (
                            SELECT manufacturer, model, sales_volume
                            FROM cars
                            WHERE year = %s AND month = %s
                            ORDER BY sales_volume DESC
                            LIMIT 10
                        ) t
                        GROUP BY manufacturer
                        ORDER BY sales DESC
                    """, (y, month_num))
                    rows = cursor.fetchall()

                    total = sum(row[1] for row in rows)
                    if total == 0:
                        monthly_shares[month_str] = None
                        continue

                    month_dict = {}
                    for mfg, sales in rows:
                        pct = round(sales / total * 100, 2)
                        month_dict[mfg] = pct
                        mfg_total_share[mfg] = mfg_total_share.get(mfg, 0) + pct
                    monthly_shares[month_str] = month_dict

            # 计算年度平均份额并取Top5厂商
            mfg_avg = {mfg: total / 12 for mfg, total in mfg_total_share.items()}
            top5_mfgs = sorted(mfg_avg.items(), key=lambda x: x[1], reverse=True)[:5]
            top5_mfgs = [mfg for mfg, _ in top5_mfgs]

            line = (
                Line()
                .add_xaxis(all_months)
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title=f"{y}年各月销量Top10车型月均前五品牌占比堆叠面积图",
                    ),
                    yaxis_opts=opts.AxisOpts(
                        name="占比(%)",
                        max_=100,
                        axislabel_opts=opts.LabelOpts(formatter="{value}%")
                    ),
                    xaxis_opts=opts.AxisOpts(
                        axislabel_opts=opts.LabelOpts(rotate=45)
                    ),
                    legend_opts=opts.LegendOpts(pos_top="5%")
                )
            )

            for mfg in top5_mfgs:
                y_data = []
                for month in all_months:
                    month_dict = monthly_shares.get(month)
                    y_data.append(month_dict.get(mfg) if month_dict and mfg in month_dict else None)

                line.add_yaxis(
                    series_name=mfg,
                    y_axis=y_data,
                    is_smooth=False,
                    is_connect_nones=True,
                    stack="Top5",
                    areastyle_opts=opts.AreaStyleOpts(opacity=0.6, color=get_mfg_color(mfg)),
                    linestyle_opts=opts.LineStyleOpts(width=1.2, color=get_mfg_color(mfg)),
                    label_opts=opts.LabelOpts(is_show=False)
                )

            duidie_timeline.add(line, str(y))

        duidie_timeline.add_schema(
            is_auto_play=True,
            play_interval=2000,
            pos_bottom="3px",
            label_opts=opts.LabelOpts(
                position="bottom",
                color="#333",
                font_size=12,
                formatter=JsCode("function(year){return year+'年';}")
            ),
            orient="horizontal",
            symbol_size=10,
            linestyle_opts=opts.LineStyleOpts(width=2),
        )

        # 箱线图
        # --------- 箱线图部分---------
        boxplot_timeline = Timeline(
            init_opts=opts.InitOpts(
                width="100%",
                height="500px",
                theme="white"
            )
        )

        for year in years:
            with conn.cursor() as cursor:
                # 获取该年各月TOP10车型的价格区间
                cursor.execute("""
                    SELECT 
                        month,
                        price_range
                    FROM (
                        SELECT 
                            month,
                            price_range,
                            ROW_NUMBER() OVER (PARTITION BY month ORDER BY sales_volume DESC) as rn
                        FROM cars
                        WHERE year = %s
                    ) t
                    WHERE rn <= 10
                    ORDER BY month
                """, (year,))

                # 处理价格数据：{month: [(min_price, max_price), ...]}
                month_prices = defaultdict(list)
                for month, price_str in cursor.fetchall():
                    try:
                        if '-' in price_str:  # "25.99-35.99"格式
                            min_p, max_p = map(float, price_str.split('-'))
                        else:  # "28.99"格式
                            min_p = max_p = float(price_str)
                        month_prices[month].append((min_p, max_p))
                    except (ValueError, TypeError) as e:
                        logger.warning(f"价格解析失败: {year}年{month}月 - {price_str} ({str(e)})")
                        continue

            # 准备箱线图数据
            box_data = []
            x_axis = []
            for month in range(1, 13):
                if month in month_prices and month_prices[month]:
                    prices = month_prices[month]
                    min_prices = [p[0] for p in prices]
                    max_prices = [p[1] for p in prices]

                    # 计算五数概括（箱线图标准格式）
                    box_data.append([
                        min(min_prices),  # 最小值
                        np.percentile(min_prices, 25),  # 下四分位
                        np.median([(p[0] + p[1]) / 2 for p in prices]),  # 中位数
                        np.percentile(max_prices, 75),  # 上四分位
                        max(max_prices)  # 最大值
                    ])
                    x_axis.append(f"{month}月")
                else:
                    # 无数据月份填充None（避免图表断裂）
                    box_data.append([None] * 5)
                    x_axis.append(f"{month}月")

            # 创建箱线图
            if any(v is not None for sublist in box_data for v in sublist):
                boxplot = (
                    Boxplot()
                    .add_xaxis(x_axis)
                    .add_yaxis(
                        "价格区间(万元)",
                        box_data,
                        itemstyle_opts=opts.ItemStyleOpts(
                            color="#c23531",
                            border_color="#871b1b",
                            border_width=2
                        )
                    )
                    .set_global_opts(
                        title_opts=opts.TitleOpts(
                            title=f"{year}年各月价格分布",
                            subtitle="                                                  箱线图展示每月TOP10车型价格区间",
                            subtitle_textstyle_opts=opts.TextStyleOpts(
                                align="left"
                            )
                        ),
                        yaxis_opts=opts.AxisOpts(
                            name="价格(万元)",
                            axislabel_opts=opts.LabelOpts(formatter="{value}万")
                        ),
                        xaxis_opts=opts.AxisOpts(
                            name="月份",
                            axislabel_opts=opts.LabelOpts(rotate=45)
                        )
                    )
                )
                boxplot_timeline.add(boxplot, str(year))

        # 配置时间轴
        boxplot_timeline.add_schema(
            is_auto_play=True,
            play_interval=3000,
            pos_bottom="3px",
            label_opts=opts.LabelOpts(
                position="bottom",
                color="#333",
                font_size=12,
                formatter=JsCode("function(year){return year+'年';}")
            )
        )

        # 页面组合与渲染
        page = Page(layout=Page.SimplePageLayout, page_title="月销量TOP10分析")
        page.add(bar, line_timeline, radar_timeline, duidie_timeline, boxplot_timeline)
        chart_html = page.render_embed()

        return render_template(
            "menu/Top10_month.html",
            years=years,
            months=months,
            current_year=year,
            current_month=month,
            current_type=data_type,
            chart_html=chart_html
        )
    finally:
        conn.close()


@menu_bp.route('/Top10_year')
def Top10_year():
    conn = get_db_connection()
    try:
        # 1. 获取所有可用年份
        with conn.cursor() as cursor:
            cursor.execute("SELECT DISTINCT year FROM cars ORDER BY year DESC")
            years = [str(row[0]) for row in cursor.fetchall()]

        # 2. 获取每个年份的销量TOP10
        yearly_top10 = {}
        for year in years:
            sql = """
            SELECT manufacturer, SUM(sales_volume) AS total_sales 
            FROM cars 
            WHERE year = %s
            GROUP BY manufacturer
            ORDER BY total_sales DESC
            LIMIT 10
            """
            with conn.cursor() as cursor:
                cursor.execute(sql, (year,))
                yearly_top10[year] = cursor.fetchall()

        # 3. 准备数据
        # 3.1 时间轴柱状图数据
        bar_data = {}
        for year, data in yearly_top10.items():
            manufacturers = [row[0] for row in data]
            sales = [float(row[1]) for row in data]
            bar_data[year] = (manufacturers, sales)

        # 3.2 时间轴折线图数据（改为显示前10名）
        line_data = {}
        for year, data in yearly_top10.items():
            manufacturers = [row[0] for row in data]  # 移除切片，显示全部10个
            sales = [float(row[1]) for row in data]
            line_data[year] = (manufacturers, sales)

        # 3.3 时间轴饼图数据
        pie_data = {}
        for year, data in yearly_top10.items():
            pie_data[year] = [(row[0], float(row[1])) for row in data]

        # 3.4 雷达图数据（比较各厂商在不同年份的表现）
        all_manufacturers = set()
        for year in years:
            manufacturers = [row[0] for row in yearly_top10[year]]
            if not all_manufacturers:
                all_manufacturers.update(manufacturers)
            else:
                all_manufacturers.intersection_update(manufacturers)

        common_manufacturers = sorted(list(all_manufacturers))[:10]  # 改为最多显示10个

        radar_data = {}
        for manufacturer in common_manufacturers:
            sales_by_year = []
            for year in years:
                rank = None
                sales = 0
                for i, row in enumerate(yearly_top10[year]):
                    if row[0] == manufacturer:
                        rank = i + 1
                        sales = float(row[1])
                        break
                sales_by_year.append((year, sales, rank))
            radar_data[manufacturer] = sales_by_year

        # 4. 创建图表
        page = Page(layout=Page.SimplePageLayout, page_title="年度销量TOP10分析")

        # 4.1 时间轴柱状图
        timeline_bar = Timeline(init_opts=opts.InitOpts(width="1400px", height="700px"))
        for year in years:
            manufacturers, sales = bar_data[year]
            bar = (
                Bar()
                .add_xaxis(manufacturers)
                .add_yaxis("销量", sales, category_gap="50%")
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title=f"{year}年汽车厂商销量TOP10",
                        title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                        subtitle_textstyle_opts=opts.TextStyleOpts(font_size=16)
                    ),
                    xaxis_opts=opts.AxisOpts(
                        axislabel_opts=opts.LabelOpts(rotate=-45, font_size=14)
                    ),
                    yaxis_opts=opts.AxisOpts(
                        name="销量(辆)",
                        name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                        axislabel_opts=opts.LabelOpts(font_size=14),
                        name_gap=25
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        trigger="axis",
                        axis_pointer_type="shadow"
                    ),
                    datazoom_opts=[opts.DataZoomOpts()]
                )
                .set_series_opts(
                    label_opts=opts.LabelOpts(is_show=True, position="top", font_size=12),
                    itemstyle_opts=opts.ItemStyleOpts(
                        opacity=0.7,
                        border_width=2
                    )
                )
            )
            timeline_bar.add(bar, year)
            # 时间线自动播放
            timeline_bar.add_schema(
                pos_left="20px",
                pos_right="20px",
                pos_bottom="3px",
                is_auto_play=True,
                play_interval=3000
            )
        page.add(timeline_bar)


        # 4.2 时间轴折线图（改为前10名）
        timeline_line = Timeline(init_opts=opts.InitOpts(width="1400px", height="700px"))
        for year in years:
            manufacturers, sales = line_data[year]
            line = (
                Line()
                .add_xaxis(manufacturers)
                .add_yaxis("销量", sales, is_smooth=True)
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title=f"{year}年汽车厂商销量TOP10趋势",  # 修改标题
                        title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                        subtitle_textstyle_opts=opts.TextStyleOpts(font_size=16)
                    ),
                    xaxis_opts=opts.AxisOpts(
                        axislabel_opts=opts.LabelOpts(font_size=14)
                    ),
                    yaxis_opts=opts.AxisOpts(
                        name="销量(辆)",
                        name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                        axislabel_opts=opts.LabelOpts(font_size=14)
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        trigger="axis"
                    ),
                    legend_opts=opts.LegendOpts(
                        pos_top="10%"
                    )
                )
                .set_series_opts(
                    label_opts=opts.LabelOpts(is_show=True, position="top", font_size=12),
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最大值"),
                            opts.MarkPointItem(type_="min", name="最小值")
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    )
                )
            )
            timeline_line.add(line, year)
        page.add(timeline_line)

        # 4.3 时间轴饼图
        timeline_pie = Timeline(init_opts=opts.InitOpts(width="1000px", height="800px"))
        for year in years:
            data = pie_data[year]
            total_sales = sum([d[1] for d in data])
            pie = (
                Pie()
                .add(
                    "",
                    data,
                    radius=["30%", "70%"],
                    rosetype="radius"
                )
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title=f"{year}年TOP10厂商销量占比",
                        subtitle=f"总销量: {total_sales:,.0f}辆",
                        title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                        subtitle_textstyle_opts=opts.TextStyleOpts(font_size=16)
                    ),
                    legend_opts=opts.LegendOpts(
                        orient="vertical",
                        pos_right="5%",
                        pos_top="15%",
                        textstyle_opts=opts.TextStyleOpts(font_size=12)
                    )
                )
                .set_series_opts(
                    label_opts=opts.LabelOpts(
                        formatter="{b}: {c}辆\n({d}%)",
                        font_size=12
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        formatter="{b}: {c}辆 ({d}%)"
                    )
                )
            )
            timeline_pie.add(pie, year)
        page.add(timeline_pie)

        # 4.4 雷达图（比较各厂商在不同年份的表现）
        # 获取所有出现过的厂商及其出现次数
        manufacturer_counts = {}
        for year in years:
            for row in yearly_top10[year]:
                manufacturer = row[0]
                manufacturer_counts[manufacturer] = manufacturer_counts.get(manufacturer, 0) + 1

        # 按出现频率排序，取前10个最常见的厂商
        common_manufacturers = sorted(
            manufacturer_counts.items(),
            key=lambda x: (-x[1], x[0])  # 先按出现次数降序，再按名称升序
        )[:10]
        common_manufacturers = [m[0] for m in common_manufacturers]  # 只保留厂商名称

        if common_manufacturers:
            radar = Radar(init_opts=opts.InitOpts(width="1400px", height="800px"))

            # 准备颜色序列
            colors = ['#c23531', '#2f4554', '#61a0a8', '#d48265', '#91c7ae',
                      '#749f83', '#ca8622', '#bda29a', '#6e7074', '#546570']

            # 准备雷达图指标
            max_sales = max(
                float(row[1])
                for year_data in yearly_top10.values()
                for row in year_data[:10]
            )
            schema = [
                opts.RadarIndicatorItem(name=year, max_=max_sales * 1.2)
                for year in sorted(years)
            ]

            # 添加各厂商数据
            for idx, manufacturer in enumerate(common_manufacturers):
                sales_by_year = []
                for year in sorted(years):
                    sales = next(
                        (float(row[1]) for row in yearly_top10[year] if row[0] == manufacturer),
                        0  # 如果某年没有该厂商，则销量为0
                    )
                    sales_by_year.append(sales)

                radar.add_schema(
                    schema=schema,
                    splitarea_opt=opts.SplitAreaOpts(
                        is_show=True,
                        areastyle_opts=opts.AreaStyleOpts(opacity=0.5)
                    ),
                    splitline_opt=opts.SplitLineOpts(is_show=True),
                )

                radar.add(
                    series_name=manufacturer,
                    data=[sales_by_year],
                    color=colors[idx % len(colors)],
                    label_opts=opts.LabelOpts(is_show=False),
                    linestyle_opts=opts.LineStyleOpts(width=2),
                    areastyle_opts=opts.AreaStyleOpts(opacity=0.1)
                )

            radar.set_global_opts(
                title_opts=opts.TitleOpts(
                    title="TOP10厂商跨年度销量对比雷达图",
                    subtitle="比较各厂商在不同年份的销量表现",
                    pos_left="center",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                    subtitle_textstyle_opts=opts.TextStyleOpts(font_size=16)
                ),
                legend_opts=opts.LegendOpts(
                    type_="scroll",
                    pos_top="5%",
                    orient="vertical",
                    pos_right="5%",
                    textstyle_opts=opts.TextStyleOpts(font_size=12)
                ),
                tooltip_opts=opts.TooltipOpts(
                    trigger="item",
                    formatter="{a}: {c}"
                )
            )

            page.add(radar)

        # 4.5 堆叠面积图（展示TOP10厂商的年度变化）
        manufacturer_scores = {}
        for year in years:
            for rank, (manufacturer, sales) in enumerate(yearly_top10[year]):
                if manufacturer not in manufacturer_scores:
                    manufacturer_scores[manufacturer] = 0
                manufacturer_scores[manufacturer] += (10 - rank)

        top10_manufacturers = sorted(  # 改为获取TOP10
            manufacturer_scores.items(),
            key=lambda x: x[1],
            reverse=True
        )[:10]
        top10_manufacturers = [m[0] for m in top10_manufacturers]

        area_data = {m: [] for m in top10_manufacturers}
        for year in years:
            year_sales = {row[0]: float(row[1]) for row in yearly_top10[year]}
            for m in top10_manufacturers:
                area_data[m].append(year_sales.get(m, 0))

        area_chart = (
            Line(init_opts=opts.InitOpts(width="1400px", height="700px"))
            .add_xaxis(years)
        )

        for m in top10_manufacturers:
            area_chart.add_yaxis(
                m,
                area_data[m],
                is_smooth=True,
                areastyle_opts=opts.AreaStyleOpts(opacity=0.5),
                stack="stack1",
                label_opts=opts.LabelOpts(is_show=False)
            )

        area_chart.set_global_opts(
            title_opts=opts.TitleOpts(
                title="TOP10厂商年度销量变化趋势",  # 修改标题
                title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                subtitle_textstyle_opts=opts.TextStyleOpts(font_size=16)
            ),
            xaxis_opts=opts.AxisOpts(
                name="年份",
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                axislabel_opts=opts.LabelOpts(font_size=14),
                boundary_gap=False
            ),
            yaxis_opts=opts.AxisOpts(
                name="销量(辆)",
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                axislabel_opts=opts.LabelOpts(font_size=14)
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="cross"
            ),
            legend_opts=opts.LegendOpts(
                pos_top="10%",
                textstyle_opts=opts.TextStyleOpts(font_size=14)
            ),
            datazoom_opts=[opts.DataZoomOpts()]
        )

        page.add(area_chart)

        # 添加空白分隔
        empty_chart = (
            Bar()
            .add_xaxis([])
            .add_yaxis("", [0])
            .set_global_opts(
                xaxis_opts=opts.AxisOpts(is_show=False),
                yaxis_opts=opts.AxisOpts(is_show=False)
            )
        )

        # 重新组织图表顺序
        final_page = Page(layout=Page.SimplePageLayout)
        final_page.add(
            timeline_bar,
            empty_chart,
            timeline_line,
            empty_chart,
            timeline_pie,
            empty_chart,
            area_chart
        )

        if common_manufacturers:
            final_page.add(
                empty_chart,
                radar
            )

        return final_page.render_embed()

    except Exception as e:
        import traceback
        error_msg = f"年度TOP10数据查询失败: {str(e)}\n\n{traceback.format_exc()}"
        print(error_msg)
        return f"<h2>数据查询错误</h2><pre>{error_msg}</pre>", 500

    finally:
        conn.close()


from pyecharts.charts import Line, Timeline  # 确保导入Line和Timeline


@menu_bp.route('/NewCar')
def NewCar():
    conn = get_db_connection()
    try:
        # 1. 定义新能源类型关键词（更全面）
        energy_keywords = [
            "新能源", "电动", "混动", "EV", "PHEV", "HEV", "EREV",
            "BEV", "燃料电池", "插电", "增程", "纯电", "氢能", "混合动力"
        ]

        # 构建模糊查询条件
        like_conditions = " OR ".join([f"model LIKE '%{kw}%'" for kw in energy_keywords])

        # 1. 首先获取总销量前十的新能源汽车厂商
        top_manufacturers_sql = f"""
        SELECT manufacturer, SUM(sales_volume) AS total_sales
        FROM cars 
        WHERE {like_conditions}
        GROUP BY manufacturer
        ORDER BY total_sales DESC
        LIMIT 10
        """

        with conn.cursor() as cursor:
            cursor.execute(top_manufacturers_sql)
            top_manufacturers_data = cursor.fetchall()

        # 提取厂商名称列表
        top_manufacturers = [row[0] for row in top_manufacturers_data]

        # 修复代码 - 转义 % 字符
        like_conditions_escaped = like_conditions.replace('%', '%%')

        # 2. 只查询这前十厂商的统计数据
        manufacturer_stats_sql = f"""
        SELECT 
            manufacturer,
            COUNT(DISTINCT model) AS model_count,
            AVG(sales_volume) AS avg_sales,
            AVG(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(price_range, '-', 1), '万', 1) AS DECIMAL)) AS avg_price,
            MAX(sales_volume) AS max_sales
        FROM cars
        WHERE {like_conditions_escaped} AND manufacturer IN ({', '.join(['%s'] * len(top_manufacturers))})
        GROUP BY manufacturer
        ORDER BY SUM(sales_volume) DESC  LIMIT 10 -- 按总销量排序
        """

        with conn.cursor() as cursor:
            cursor.execute(manufacturer_stats_sql, top_manufacturers)
            stats_data = cursor.fetchall()

        # 2. 查询所有新能源车型（使用DISTINCT去重）
        energy_sql = f"""
        SELECT DISTINCT model, manufacturer 
        FROM cars 
        WHERE {like_conditions}
        """

        # 3. 按厂商统计新能源车型数量
        manufacturer_sql = f"""
        SELECT manufacturer, COUNT(DISTINCT model) AS model_count 
        FROM cars 
        WHERE {like_conditions}
        GROUP BY manufacturer
        ORDER BY model_count DESC
        """

        # 4. 新能源车销量TOP10
        sales_sql = f"""
        SELECT model, manufacturer, SUM(sales_volume) AS total_sales 
        FROM cars 
        WHERE {like_conditions}
        GROUP BY model, manufacturer
        ORDER BY total_sales DESC
        LIMIT 10
        """

        # 5. 年度销量趋势分析
        trend_sql = f"""
        SELECT year, SUM(sales_volume) AS total_sales 
        FROM cars 
        WHERE {like_conditions}
        GROUP BY year
        ORDER BY year
        """

        # 6. 价格区间分析（修复后的版本）
        price_sql = f"""
        SELECT 
            CASE 
                WHEN CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(price_range, '-', 1), '万', 1) AS DECIMAL) < 10 THEN '10万以下'
                WHEN CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(price_range, '-', 1), '万', 1) AS DECIMAL) BETWEEN 10 AND 20 THEN '10-20万'
                WHEN CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(price_range, '-', 1), '万', 1) AS DECIMAL) BETWEEN 20 AND 30 THEN '20-30万'
                ELSE '30万以上'
            END AS price_group,
            COUNT(DISTINCT model) AS model_count
        FROM cars
        WHERE {like_conditions}
        GROUP BY price_group
        """

        # 7. 各年度新能源占比（需要总销量数据）
        total_sales_sql = """
        SELECT year, SUM(sales_volume) AS total_all_sales 
        FROM cars 
        GROUP BY year
        ORDER BY year
        """

        # 新增图表6：不同类型新能源车销量分析
        type_trend_sql = f"""
        SELECT 
            year,
            CASE
                WHEN model LIKE '%纯电%' OR model LIKE '%BEV%' OR model LIKE '%EV%' THEN '纯电动汽车(BEV)'
                WHEN model LIKE '%插电%' OR model LIKE '%PHEV%' THEN '插电式混合动力(PHEV)'
                WHEN model LIKE '%增程%' OR model LIKE '%EREV%' THEN '增程式电动车(EREV)'
                WHEN model LIKE '%燃料电池%' OR model LIKE '%氢能%' OR model LIKE '%FCEV%' THEN '燃料电池车(FCEV)'
                WHEN model LIKE '%混动%' OR model LIKE '%HEV%' THEN '混合动力(HEV)'
                ELSE '其他新能源'
            END AS energy_type,
            SUM(sales_volume) AS total_sales
        FROM cars
        WHERE {like_conditions}
        GROUP BY year, energy_type
        ORDER BY year
        """

        # 执行所有查询
        with conn.cursor() as cursor:
            # 新能源车型
            cursor.execute(energy_sql)
            new_energy_models = cursor.fetchall()

            # 厂商车型数量
            cursor.execute(manufacturer_sql)
            manufacturer_counts = cursor.fetchall()

            # 销量TOP10
            cursor.execute(sales_sql)
            top_sales = cursor.fetchall()

            # 年度销量趋势
            cursor.execute(trend_sql)
            trend_data = cursor.fetchall()

            # 价格区间
            cursor.execute(price_sql)
            price_data = cursor.fetchall()

            # 年度总销量
            cursor.execute(total_sales_sql)
            total_sales_data = cursor.fetchall()

            # 新能源各类型销量
            cursor.execute(type_trend_sql)
            type_trend_data = cursor.fetchall()

            # 雷达图展示各厂商实力
            cursor.execute(top_manufacturers_sql)
            top_manufacturers = [row[0] for row in cursor.fetchall()]

            # 雷达图展示各厂商实力
            cursor.execute(manufacturer_stats_sql, top_manufacturers)  # 使用转义后的 SQL
            stats_data = cursor.fetchall()

        # 处理数据
        # 所有新能源车型列表
        model_list = [{"model": row[0], "manufacturer": row[1]} for row in new_energy_models]

        # 厂商车型数量统计
        manufacturers = [row[0] for row in manufacturer_counts]
        model_counts = [row[1] for row in manufacturer_counts]

        # 销量TOP10
        top_models = [row[0] for row in top_sales]
        top_sales_values = [row[2] for row in top_sales]

        # 年度销量趋势
        trend_years = [str(row[0]) for row in trend_data]  # 重命名为trend_years
        energy_sales = [row[1] for row in trend_data]

        # 价格区间分布
        price_groups = [row[0] for row in price_data]
        price_counts = [row[1] for row in price_data]

        # 年度总销量数据
        total_sales_dict = {str(row[0]): row[1] for row in total_sales_data}

        type_trend_years = sorted(set([row[0] for row in type_trend_data]))  # 重命名为type_trend_years
        energy_types = sorted(set([row[1] for row in type_trend_data]))

        # 3. 时间轴饼图数据使用正确的年份数据
        timeline_pie_data = []
        for year, energy_sale in zip(trend_years, energy_sales):  # 使用trend_years
            if year in total_sales_dict:
                total_sale = total_sales_dict[year]
                percent = round(energy_sale / total_sale * 100, 2)
                timeline_pie_data.append((
                    year,
                    [("新能源车", energy_sale), ("传统燃油车", total_sale - energy_sale)],
                    f"{year}年新能源占比: {percent}%"
                ))

        # 4. 堆叠柱状图数据处理（使用type_trend_years）
        type_sales_by_year = {etype: [] for etype in energy_types}
        for year in type_trend_years:  # 使用type_trend_years
            year_data = [row for row in type_trend_data if row[0] == year]
            for etype in energy_types:
                sales = next((row[2] for row in year_data if row[1] == etype), 0)
                type_sales_by_year[etype].append(sales)

        # 处理雷达图数据
        stats_data_for_radar = []
        for row in stats_data:
            manufacturer, model_count, avg_sales, avg_price, max_sales = row
            stats_data_for_radar.append((
                manufacturer,
                model_count,
                float(avg_sales) if avg_sales is not None else 0.0,  # 转换为float，处理None
                float(avg_price) if avg_price is not None else 0.0,  # 转换为float，处理None
                max_sales
            ))

        # 计算最大值
        if stats_data_for_radar:
            max_model_count = max([row[1] for row in stats_data_for_radar])
            max_avg_sales = max([row[2] for row in stats_data_for_radar])
            max_avg_price = max([row[3] for row in stats_data_for_radar])
            max_max_sales = max([row[4] for row in stats_data_for_radar])
        else:
            # 如果没有数据，设置默认最大值
            max_model_count = max_avg_sales = max_avg_price = max_max_sales = 1

        indicators = [
            opts.RadarIndicatorItem(name="车型数量", max_=max_model_count * 1.2),
            opts.RadarIndicatorItem(name="平均销量", max_=max_avg_sales * 1.2),
            opts.RadarIndicatorItem(name="平均售价(万)", max_=max_avg_price * 1.2),
            opts.RadarIndicatorItem(name="最高销量", max_=max_max_sales * 1.2)
        ]

        radar_data = []
        for row in stats_data_for_radar:
            manufacturer, model_count, avg_sales, avg_price, max_sales = row
            radar_data.append(
                opts.RadarItem(
                    name=manufacturer,
                    value=[model_count, avg_sales, avg_price, max_sales],
                    symbol="circle",
                    symbol_size=8
                )
            )

        # 创建图表（增大尺寸，增加间距）
        final_page = Page(layout=Page.SimplePageLayout, page_title="新能源车分析")

        radar_chart = (
            Radar(init_opts=opts.InitOpts(width="1400px", height="800px"))
            .add_schema(
                schema=indicators,
                splitarea_opt=opts.SplitAreaOpts(is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=0.1)),
                textstyle_opts=opts.TextStyleOpts(font_size=14)
            )
            .add(
                series_name="厂商",
                data=radar_data,
                linestyle_opts=opts.LineStyleOpts(width=2),
                areastyle_opts=opts.AreaStyleOpts(opacity=0.3),
                # 确保数据点标签不显示
                label_opts=opts.LabelOpts(False)
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(
                    title="销量前十厂商新能源多维度实力对比",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                    pos_top="1%"
                ),
                legend_opts=opts.LegendOpts(
                    textstyle_opts=opts.TextStyleOpts(font_size=14),
                    orient="vertical",
                    pos_right="6%",
                    pos_top="center"
                ),
                tooltip_opts=opts.TooltipOpts(
                    formatter=JsCode(
                        "function(params) {"
                        "   var value = params.value;"
                        "   return params.name + '<br/>' + "
                        "          '车型数量: ' + value[0] + '款<br/>' +"
                        "          '平均销量: ' + value[1].toFixed(0) + '辆<br/>' + "
                        "          '平均售价: ' + value[2].toFixed(2) + '万元<br/>' + "
                        "          '最高销量: ' + value[3] + '辆';"
                        "}"
                    ),
                    textstyle_opts=opts.TextStyleOpts(font_size=14)
                )
            )
        )

        # 图表1：厂商新能源车型数量（柱状图）
        bar1 = (
            Bar(init_opts=opts.InitOpts(width="1400px", height="600px"))
            .add_xaxis(manufacturers)
            .add_yaxis("新能源车型数量", model_counts, category_gap="50%")
            .set_global_opts(
                title_opts=opts.TitleOpts(
                    title="各厂商新能源车型数量",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                    pos_top="1%"),
                xaxis_opts=opts.AxisOpts(
                    axislabel_opts=opts.LabelOpts(rotate=-45, font_size=14),
                    name="厂商",
                    name_location="middle",
                    name_gap=30
                ),
                yaxis_opts=opts.AxisOpts(
                    name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                    axislabel_opts=opts.LabelOpts(font_size=16),
                    name="车型数量", name_location="middle", name_gap=40),
                datazoom_opts=[opts.DataZoomOpts()],
                legend_opts=opts.LegendOpts(
                    textstyle_opts=opts.TextStyleOpts(font_size=16))
            )
            .set_series_opts(
                label_opts=opts.LabelOpts(is_show=True, position="top")
            )
        )
        final_page.add(bar1)

        # 图表2：新能源车销量TOP10（柱状图）
        bar2 = (
            Bar(init_opts=opts.InitOpts(width="1400px", height="600px"))
            .add_xaxis(top_models)
            .add_yaxis("总销量", top_sales_values, category_gap="50%")
            .set_global_opts(
                title_opts=opts.TitleOpts(
                    title="新能源车型销量TOP10",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                    pos_top="1%"),
                xaxis_opts=opts.AxisOpts(
                    axislabel_opts=opts.LabelOpts(rotate=-45, font_size=14),
                    name="车型",
                    name_location="middle",
                    name_gap=30
                ),
                yaxis_opts=opts.AxisOpts(
                    name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                    name="销量(辆)", name_location="middle", name_gap=60,
                    axislabel_opts=opts.LabelOpts(font_size=14)),
                datazoom_opts=[opts.DataZoomOpts()],
                legend_opts=opts.LegendOpts(
                    textstyle_opts=opts.TextStyleOpts(font_size=16))
            )
            .set_series_opts(
                label_opts=opts.LabelOpts(is_show=True, position="top")
            )
        )
        final_page.add(bar2)

        # 图表3：年度销量趋势（折线图）- 修复初始化
        line = (
            Line(init_opts=opts.InitOpts(width="1400px", height="600px"))
            .add_xaxis(trend_years)
            .add_yaxis(
                "新能源车销量",
                energy_sales,
                is_smooth=True,
                label_opts=opts.LabelOpts(is_show=True),
                markpoint_opts=opts.MarkPointOpts(
                    data=[
                        opts.MarkPointItem(type_="max", name="最高销量"),
                        opts.MarkPointItem(type_="min", name="最低销量")
                    ]
                ),
                markline_opts=opts.MarkLineOpts(
                    data=[opts.MarkLineItem(type_="average", name="平均值")]
                )
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(
                    title="新能源车年度销量趋势",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                    pos_top="1%"),
                xaxis_opts=opts.AxisOpts(
                    axislabel_opts=opts.LabelOpts(font_size=14),
                    name="年份",
                    name_location="middle",
                    name_gap=30,
                    boundary_gap=False
                ),
                yaxis_opts=opts.AxisOpts(
                    name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                    name="销量(辆)",
                    name_location="middle",
                    name_gap=60,
                    axislabel_opts=opts.LabelOpts(font_size=14)
                ),
                tooltip_opts=opts.TooltipOpts(trigger="axis"),
                datazoom_opts=[opts.DataZoomOpts()],
                legend_opts=opts.LegendOpts(
                    textstyle_opts=opts.TextStyleOpts(font_size=16))
            )
        )
        final_page.add(line)

        # 图表4：价格区间分布（饼图）
        pie_price = (
            Pie(init_opts=opts.InitOpts(width="1000px", height="700px"))
            .add(
                "",
                [list(z) for z in zip(price_groups, price_counts)],
                radius=["30%", "60%"],
                center=["50%", "50%"]
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(
                    title="新能源车价格区间分布",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                    pos_top="1%",
                    pos_left="center"
                ),
                legend_opts=opts.LegendOpts(
                    orient="vertical",
                    pos_right="-0.5%",
                    pos_top="center",
                    textstyle_opts=opts.TextStyleOpts(font_size=16)
                )
            )
            .set_series_opts(
                label_opts=opts.LabelOpts(
                    formatter="{b}: {c}款 ({d}%)",
                    font_size=14
                ),
                tooltip_opts=opts.TooltipOpts(
                    formatter="{b}: {c}款 ({d}%)"
                )
            )
        )
        final_page.add(pie_price)

        # 图表5：时间轴饼图 - 各年度新能源占比
        timeline = Timeline(
            init_opts=opts.InitOpts(width="1000px", height="700px")
        )

        for year, data, title in timeline_pie_data:
            pie = (
                Pie()
                .add(
                    "",
                    data,
                    radius=["40%", "70%"],
                    center=["50%", "50%"]
                )
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title=title,
                        title_textstyle_opts=opts.TextStyleOpts(font_size=20),
                        pos_top="1%",
                        pos_left="center"
                    ),
                    legend_opts=opts.LegendOpts(
                        orient="vertical",
                        pos_right="1%",
                        pos_top="center",
                        textstyle_opts=opts.TextStyleOpts(font_size=16)
                    )
                )
                .set_series_opts(
                    label_opts=opts.LabelOpts(
                        formatter="{b}: {c}辆\n({d}%)",
                        font_size=14
                    ),
                    tooltip_opts=opts.TooltipOpts(
                        formatter="{b}: {c}辆 ({d}%)"
                    )
                )
            )
            timeline.add(pie, year)

        final_page.add(timeline)

        # 修改后（正确代码）
        empty_chart = (
            Bar(init_opts=opts.InitOpts(width="100%", height="50px"))
            .add_xaxis([])
            .add_yaxis("", [])
            .set_global_opts(
                xaxis_opts=opts.AxisOpts(is_show=False),
                yaxis_opts=opts.AxisOpts(is_show=False)
            )
        )

        # 图表7：堆叠柱状图
        stacked_bar = (
            Bar(init_opts=opts.InitOpts(width="1400px", height="700px"))
            .add_xaxis([str(y) for y in type_trend_years])
        )

        # 添加每种能源类型的堆叠数据
        for etype in energy_types:
            stacked_bar.add_yaxis(
                etype,
                type_sales_by_year[etype],
                stack="stack1",  # 使用相同的stack名称实现堆叠效果
                label_opts=opts.LabelOpts(is_show=False),
                category_gap="50%"
            )

        # 设置全局选项
        stacked_bar.set_global_opts(
            title_opts=opts.TitleOpts(
                title="不同类型新能源车年度销量趋势",
                title_textstyle_opts=opts.TextStyleOpts(
                    font_size=20,
                    font_weight="bold"
                ),
                pos_top="1%"
            ),
            xaxis_opts=opts.AxisOpts(
                name="年份",
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                name_location="middle",
                name_gap=30,
                axislabel_opts=opts.LabelOpts(font_size=14)
            ),
            yaxis_opts=opts.AxisOpts(
                name="销量(辆)",
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                name_location="middle",
                name_gap=80,
                axislabel_opts=opts.LabelOpts(font_size=14)
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="shadow",
                textstyle_opts=opts.TextStyleOpts(font_size=14)
            ),
            legend_opts=opts.LegendOpts(
                textstyle_opts=opts.TextStyleOpts(font_size=16),
                pos_right="1%",
                pos_top="top"
            ),
            datazoom_opts=[opts.DataZoomOpts()]
        )
        final_page.add(stacked_bar)

        # 重新组织图表顺序，添加间距
        final_page = Page(layout=Page.SimplePageLayout)
        final_page.add(
            radar_chart,
            empty_chart,
            bar1,
            empty_chart,
            bar2,
            empty_chart,
            line,
            empty_chart,
            pie_price,
            empty_chart,
            timeline,
            empty_chart,
            stacked_bar
        )

        return final_page.render_embed()

    except Exception as e:
        # 错误处理 - 直接返回错误信息
        import traceback
        error_msg = f"新能源数据查询失败: {str(e)}\n\n{traceback.format_exc()}"
        print(error_msg)
        return f"<h2>数据查询错误</h2><pre>{error_msg}</pre>", 500

    finally:
        conn.close()


@menu_bp.route('/Top_sum')
def Top_sum():
    conn = get_db_connection()
    try:
        # 这是总和
        # 查询每个厂商的总销量

        sql = """
            SELECT manufacturer, SUM(sales_volume) AS total_sales
            FROM cars  -- 替换为您的表名
            GROUP BY manufacturer
            ORDER BY total_sales DESC
            """
        with conn.cursor() as cursor:
            cursor.execute(sql)
            sales_data = cursor.fetchall()

        # 数据处理
        manufacturers = [item[0] for item in sales_data]
        sales_values = [item[1] for item in sales_data]

        # 创建条形图（所有厂商）
        bar = (
            Bar(init_opts=opts.InitOpts(width="1400px", height="600px"))
            # Bar()
            .add_xaxis(manufacturers)
            .add_yaxis("销量", sales_values, label_opts=opts.LabelOpts(is_show=False))
            .set_global_opts(
                title_opts=opts.TitleOpts(title="各汽车厂商总销量", subtitle="近年总销量数据"),
                xaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(rotate=-45)),
                datazoom_opts=[opts.DataZoomOpts()],  # 添加缩放滚动条

            )
        )

        # 创建饼图
        # 获取前10厂商数据
        top_10_manu = [m for m in manufacturers[:10]]
        top_10_sales = [s for s in sales_values[:10]]

        pie = (
            Pie(init_opts=opts.InitOpts(width="1400px", height="600px"))
            .add(
                series_name="销量占比",
                data_pair=[list(z) for z in zip(top_10_manu, top_10_sales)],
                radius=["30%", "60%"],
                label_opts=opts.LabelOpts(
                    formatter="{b}: {d}% ({c}辆)",
                    rich={
                        'value': {'fontSize': 14, 'color': '#999'},
                        'percent': {'color': '#999'}
                    }
                )
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(title="厂商销量Top10占比"),
                legend_opts=opts.LegendOpts(orient="vertical", pos_top="10%", pos_left="0%"),
                tooltip_opts=opts.TooltipOpts(
                    formatter="{b}: {c}辆 ({d}%)",
                )
            )
        )

        page = charts.Page()
        page.add(bar, pie)

        return page.render_embed()
    finally:
        conn.close()



@menu_bp.route('/Top_car_type')
def Top_car_type():
    conn = get_db_connection()
    try:
        # 1. 查询销量TOP10的车型
        top_models_sql = """
        SELECT model, SUM(sales_volume) AS total_sales 
        FROM cars 
        GROUP BY model 
        ORDER BY total_sales DESC 
        LIMIT 10
        """

        # 2. 查询各车型不同年份的销量数据
        model_year_sales_sql = """
        SELECT model, year, SUM(sales_volume) AS total_sales 
        FROM cars 
        WHERE model IN (%s)
        GROUP BY model, year 
        ORDER BY model, year
        """

        # 3. 查询各车型不同月份的销量数据
        model_month_sales_sql = """
        SELECT model, month, SUM(sales_volume) AS total_sales 
        FROM cars 
        WHERE model IN (%s)
        GROUP BY model, month 
        ORDER BY model, month
        """

        # 4. 查询各车型的制造商信息
        model_manufacturer_sql = """
        SELECT DISTINCT model, manufacturer 
        FROM cars 
        WHERE model IN (%s)
        """

        # 执行查询获取销量TOP10的车型
        with conn.cursor() as cursor:
            cursor.execute(top_models_sql)
            top_models_data = cursor.fetchall()

            # 提取TOP10车型名称
            top_models = [row[0] for row in top_models_data]

            # 构建IN子句的参数占位符
            in_clause = ', '.join(['%s'] * len(top_models))

            # 查询各车型不同年份的销量
            cursor.execute(model_year_sales_sql % in_clause, top_models)
            model_year_sales = cursor.fetchall()

            # 查询各车型不同月份的销量
            cursor.execute(model_month_sales_sql % in_clause, top_models)
            model_month_sales = cursor.fetchall()

            # 查询各车型的制造商
            cursor.execute(model_manufacturer_sql % in_clause, top_models)
            model_manufacturers = cursor.fetchall()

        # 处理数据
        # TOP10车型及总销量
        top_model_names = [row[0] for row in top_models_data]
        top_model_sales = [row[1] for row in top_models_data]

        # 构建车型-制造商映射
        model_manufacturer_map = {row[0]: row[1] for row in model_manufacturers}

        # 构建车型-年份-销量数据结构
        model_year_data = {}
        for model, year, sales in model_year_sales:
            if model not in model_year_data:
                model_year_data[model] = {"years": [], "sales": []}
            model_year_data[model]["years"].append(str(year))
            model_year_data[model]["sales"].append(sales)

        # 构建车型-月份-销量数据结构
        model_month_data = {}
        for model, month, sales in model_month_sales:
            if model not in model_month_data:
                model_month_data[model] = {"months": [], "sales": []}
            model_month_data[model]["months"].append(str(month))
            model_month_data[model]["sales"].append(sales)

        # 创建多种可视化图表

        # 图表1：TOP10车型总销量柱状图
        bar_chart = (
            Bar(init_opts=opts.InitOpts(width="1400px", height="600px"))
            .add_xaxis(top_model_names)
            .add_yaxis("总销量", top_model_sales, category_gap="50%")
            .set_global_opts(
                title_opts=opts.TitleOpts(
                    title="车型销量TOP10",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=24),
                    pos_top="3%"
                ),
                xaxis_opts=opts.AxisOpts(
                    axislabel_opts=opts.LabelOpts(rotate=-45, font_size=14),
                    name="车型",
                    name_location="middle",
                    name_gap=30
                ),
                yaxis_opts=opts.AxisOpts(
                    name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                    name="销量(辆)",
                    name_location="middle",
                    name_gap=60,
                    axislabel_opts=opts.LabelOpts(font_size=14)
                ),
                datazoom_opts=[opts.DataZoomOpts()],
                legend_opts=opts.LegendOpts(
                    textstyle_opts=opts.TextStyleOpts(font_size=16)
                )
            )
            .set_series_opts(
                label_opts=opts.LabelOpts(is_show=True, position="top")
            )
        )

        # 图表2：TOP10车型分年份销量折线图
        line_chart = (
            Line(init_opts=opts.InitOpts(width="1400px", height="700px"))
        )

        # 获取所有年份
        all_years = set()
        for model_data in model_year_data.values():
            all_years.update(model_data["years"])
        all_years = sorted(all_years)

        line_chart.add_xaxis(all_years)

        # 为每个车型添加折线
        for model in top_model_names:
            if model in model_year_data:
                line_chart.add_yaxis(
                    f"{model} ({model_manufacturer_map.get(model, '未知')})",
                    model_year_data[model]["sales"],
                    is_smooth=True,
                    label_opts=opts.LabelOpts(is_show=False),
                    markpoint_opts=opts.MarkPointOpts(
                        data=[
                            opts.MarkPointItem(type_="max", name="最高销量"),
                            opts.MarkPointItem(type_="min", name="最低销量")
                        ]
                    ),
                    markline_opts=opts.MarkLineOpts(
                        data=[opts.MarkLineItem(type_="average", name="平均值")]
                    )
                )

        line_chart.set_global_opts(
            title_opts=opts.TitleOpts(
                title="TOP10车型分年份销量趋势",
                title_textstyle_opts=opts.TextStyleOpts(font_size=24),
                pos_top="2%"
            ),
            legend_opts=opts.LegendOpts(
                orient="horizontal",
                type_="scroll",
                pos_left="30%",
                pos_top="5%",
                item_gap=20,
                item_width=40
            ),
            xaxis_opts=opts.AxisOpts(
                name="年份",
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                name_location="middle",
                name_gap=30,
                boundary_gap=False
            ),
            yaxis_opts=opts.AxisOpts(
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                name="销量(辆)",
                name_location="middle",
                name_gap=60,
                axislabel_opts=opts.LabelOpts(font_size=14)
            ),
            tooltip_opts=opts.TooltipOpts(trigger="axis"),
            datazoom_opts=[opts.DataZoomOpts()]
        )

        # 图表3：TOP10车型销量占比饼图
        pie_chart = (
            Pie(init_opts=opts.InitOpts(width="1000px", height="700px"))
            .add(
                "",
                [list(z) for z in zip(top_model_names, top_model_sales)],
                radius=["30%", "60%"],
                center=["50%", "50%"]
            )
            .set_global_opts(
                title_opts=opts.TitleOpts(
                    title="TOP10车型销量占比",
                    title_textstyle_opts=opts.TextStyleOpts(font_size=24),
                    pos_top="1%",
                    pos_left="center"
                ),
                legend_opts=opts.LegendOpts(
                    orient="vertical",
                    pos_right="1%",
                    pos_top="center",
                    textstyle_opts=opts.TextStyleOpts(font_size=14)
                )
            )
            .set_series_opts(
                label_opts=opts.LabelOpts(
                    formatter="{b}: {c}辆 ({d}%)",
                    font_size=14
                ),
                tooltip_opts=opts.TooltipOpts(
                    formatter="{b}: {c}辆 ({d}%)"
                )
            )
        )

        # 图表4：TOP10车型分月份销量堆叠柱状图
        stacked_bar_chart = (
            Bar(init_opts=opts.InitOpts(width="1400px", height="700px"))
        )

        # 获取所有月份
        all_months = set()
        for model_data in model_month_data.values():
            all_months.update(model_data["months"])
        all_months = sorted(all_months)

        stacked_bar_chart.add_xaxis(all_months)

        # 为每个车型添加堆叠数据
        for model in top_model_names:
            if model in model_month_data:
                stacked_bar_chart.add_yaxis(
                    f"{model} ({model_manufacturer_map.get(model, '未知')})",
                    model_month_data[model]["sales"],
                    stack="sales",  # 使用相同的stack名称实现堆叠效果
                    label_opts=opts.LabelOpts(is_show=False),
                    category_gap="50%"
                )

        stacked_bar_chart.set_global_opts(
            title_opts=opts.TitleOpts(
                title="TOP10车型分月份销量分布",
                title_textstyle_opts=opts.TextStyleOpts(
                    font_size=24,
                    font_weight="bold"
                ),
                pos_top="2%"
            ),
            legend_opts=opts.LegendOpts(
                orient="horizontal",
                type_="scroll",
                pos_left="30%",
                pos_top="5%",
                item_gap=20,
                item_width=40
            ),
            xaxis_opts=opts.AxisOpts(
                name="月份",
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                name_location="middle",
                name_gap=30,
                axislabel_opts=opts.LabelOpts(font_size=14)
            ),
            yaxis_opts=opts.AxisOpts(
                name="销量(辆)",
                name_textstyle_opts=opts.TextStyleOpts(font_size=16),
                name_location="middle",
                name_gap=80,
                axislabel_opts=opts.LabelOpts(font_size=14)
            ),
            tooltip_opts=opts.TooltipOpts(
                trigger="axis",
                axis_pointer_type="shadow",
                textstyle_opts=opts.TextStyleOpts(font_size=14)
            ),
            datazoom_opts=[opts.DataZoomOpts()]
        )

        # 图表5：车型-制造商关系桑基图（如果数据足够）
        # 由于桑基图需要源-目标-值关系，这里简化处理
        if len(model_manufacturers) > 0:
            # 构建制造商列表
            manufacturers = list(set([row[1] for row in model_manufacturers]))

            # 构建桑基图数据
            sankey_data = []
            for model, manufacturer in model_manufacturers:
                sankey_data.append({
                    "source": manufacturer,
                    "target": model,
                    "value": next((sales for m, s in top_models_data if m == model), 0)
                })

            # 创建桑基图
            sankey_chart = (
                Sankey(init_opts=opts.InitOpts(width="1400px", height="700px"))
                .add(
                    "车型-制造商关系",
                    nodes=[{'name': n} for n in manufacturers + top_model_names],
                    links=sankey_data,
                    linestyle_opt=opts.LineStyleOpts(opacity=0.8, curve=0.5, color="source"),
                    label_opts=opts.LabelOpts(position="right")
                )
                .set_global_opts(
                    title_opts=opts.TitleOpts(
                        title="车型-制造商关系桑基图",
                        title_textstyle_opts=opts.TextStyleOpts(font_size=24),
                        pos_top="0%"
                    ),
                    toolbox_opts=opts.ToolboxOpts(is_show=True)
                )
            )
        else:
            sankey_chart = None

        # 创建空图表用于间隔
        empty_chart = (
            Bar(init_opts=opts.InitOpts(width="100%", height="100px"))
            .add_xaxis([])
            .add_yaxis("", [])
            .set_global_opts(
                xaxis_opts=opts.AxisOpts(is_show=False),
                yaxis_opts=opts.AxisOpts(is_show=False)
            )
        )

        # 整合所有图表到Page对象
        final_page = Page(layout=Page.SimplePageLayout)
        final_page.add(
            bar_chart,
            empty_chart,
            line_chart,
            empty_chart,
            pie_chart,
            empty_chart,
            stacked_bar_chart
        )

        # 如果有桑基图数据，也添加进去
        if sankey_chart:
            final_page.add(empty_chart, sankey_chart)

        # 将图表渲染为HTML并传递给模板
        chart_html = final_page.render_embed()

        return render_template('menu/Top_car_type.html',
                               chart_html=chart_html,
                               top_models=top_model_names,
                               top_sales=top_model_sales,
                               # 修复：确保传递的是制造商列表，并且长度与车型列表一致
                               model_manufacturers=[model_manufacturer_map.get(m, "未知") for m in top_model_names])

    except Exception as e:
        # 错误处理
        error_msg = f"车型数据查询失败: {str(e)}\n\n{traceback.format_exc()}"
        print(error_msg)
        return f"<h2>数据查询错误</h2><pre>{error_msg}</pre>", 500

    finally:
        conn.close()



@menu_bp.route('/select_example')
def select_example():
    goods_list = db.session.query(Goods).all()
    test_goods_list = []
    for good in goods_list:
        test_goods_list.append(
            {
                'a': good.a,
                'b': good.b,
                'c': good.c
            }
        )
    return jsonify(test_goods_list)
